/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package store;

import inventory.Product;
import inventory.Request;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Queue;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.ejb.Stateless;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import javax.jws.WebService;

/**
 *
 * @author User
 */
@WebService(serviceName = "StoreWebService")
@Stateless()
public class StoreWebService {
    private Connection dbcon;
    private final int SKEY_LEN = 20;
    private Map<String, Integer> skeys;
    private Map<Integer, String> userKeys;
    private Map<String, Integer> userStore;
    private List<Store> storeList;
    private Queue<Request> requests;
    private Map<String, Queue<String>> userNotes;
    private int rqctr;
    
    public StoreWebService() {
        skeys = new HashMap<>();
        userKeys = new HashMap<>();
        requests = new LinkedList<>();
        userNotes = new HashMap<>();
        userStore = new HashMap<>();
        rqctr = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            dbcon = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/dbwebservice", "root", "");
            storeList = new ArrayList<>();
            Statement stmt;
            ResultSet rs;
            
            stmt = dbcon.createStatement();
            stmt.execute("SELECT * FROM storeinfo");
            rs = stmt.getResultSet();

            while (rs.next()) {
                Store s = new Store(rs.getInt("idsto"), rs.getString("locsto"),
                        rs.getString("namesto"), rs.getString("contactsto"));
                storeList.add(s);
            }
        
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(StoreWebService.class.getName())
                    .log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Web service operation
     * @return The list of tables in the dbwebservice database.
     */
    @WebMethod(operationName = "dbTest")
    public String dbTest() {
        String result = "";
        Statement stmt;
        ResultSet rs;
        try {
            stmt = dbcon.createStatement();
            stmt.execute("SHOW TABLES");
            rs = stmt.getResultSet();
            
            while (rs.next()) {
                result += rs.getString(1) + "\n";
            }
        } catch (SQLException ex) {
            System.out.println("Error:" + ex.getMessage());
        }
        return result;
    }
    
    /**
     * Web service operation for logging a user in. Returns a status message
     * which reflects to what operations may have happened. The status message
     * would be prepended with an integer status code. 200 would indicate that
     * the login was successful. An integer status 401 would be an indication
     * that the login was unauthorized. The status code and the message is
     * separated by a single <b>:</b>. Additionally a unique session key would
     * be produced for verification purposes to be used in other operations.
     * Only the latest session key could be used by a user to ensure single
     * logins, meaning logging in the next time in the system would practically
     * disable operations that could happen in the last active session by the
     * user.
     * Ex: "200:Login success."
     * @param username the username to be logged in.
     * @param password a Clear-Text password used for the login.
     * @return [0] the status of the operation and [1] the session key.
     */
    @WebMethod(operationName = "login")
    public String[] login(@WebParam(name = "username") String username,
            @WebParam(name = "password") String password) {
        String status;
        String sk = null;
        PreparedStatement stmt;
        ResultSet rs;
        try {
            String dbpass;
            stmt = dbcon.prepareStatement("SELECT idsu, pwsu, idsto "
                    + "FROM storeuser WHERE unsu=?");
            stmt.setString(1, username);
            if (stmt.execute()) {
                rs = stmt.getResultSet();
                if (!rs.next()) {
                    status = "401:Unable to login.";
                    System.out.println("Login "
                            + "Username non-existant: " + username);
                } else {
                    dbpass = rs.getString("pwsu");
                    if (dbpass.equals(password)) {
                        status = "200:Login success.";
                        sk = nextSessionKey();
                        int id = rs.getInt("idsu");
                        int idsto = rs.getInt("idsto");
                        userKeys.put(id, sk);
                        userNotes.put(sk, new LinkedList<String>());
                        skeys.put(sk, id);
                        userStore.put(sk, idsto);
                        System.out.println("Login "
                                + "Map Size: " + userKeys.size());
                    } else {
                        status = "401:Unable to login.";        
                    }
                }
            } else {
                status = "401:Unable to login.";
            }
        } catch (SQLException ex) {
            System.out.println("Error:" + ex.getMessage());
            status = "500:Internal service error.";
        }
        return new String[]{status, sk};
    }

    /**
     * Web service operation for registering a user into the database.
     * Returns a status message that would indicate as to whether the operation
     * is successful or not. The status message is composed of two elements.
     * The first being an <code>int</code> status code, and a 
     * <code>String</code> message.
     * @param username the username of the user to be registered, it should not
     * be the same as any username of currently registered users.
     * @param password clear-text password of the user.
     * @param storeid the store id where the user is to be registered.
     * @param fullname the full name of the user.
     * @return the status of the operation.
     */
    @WebMethod(operationName = "register")
    public String register(@WebParam(name = "username") String username,
            @WebParam(name = "password") String password,
            @WebParam(name = "fullname") String fullname,
            @WebParam(name = "storeid") int storeid) {
        String status;
        PreparedStatement stmt;
        try {
            stmt = dbcon.prepareStatement("INSERT INTO storeuser(unsu, pwsu, "
                    + "fnsu, idsto) VALUES(?,?,?,?)");
            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setString(3, fullname);
            stmt.setInt(4, storeid);
            if (stmt.executeUpdate() == 1) {
                status =  "200:Registration success.";
            } else {
                status = "401:Registration failed.";
            }
        } catch(SQLException ex) {
            System.out.println("Error:" + ex.getMessage());
            if (ex.getMessage().contains("Duplicate entry")) {
                status = "401:Username already taken.";
            } else {
                status = "501:Internal Service Error.";
            }
        }
        System.out.println(status);
        return status;
    }

    /**
     * Web service operation for determining the sales of a specific item.
     * @param id
     * @param productname is the description/brand/kind of the item.
     * @param price is the price of the item.
     * @param quantity is the quantity of the item.
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "sale")
    public Integer sale(@WebParam(name = "id") int id,
            @WebParam(name = "productname") String productname,
            @WebParam(name = "price") double price,
            @WebParam(name = "quantity") int quantity,
            @WebParam(name = "skey") String skey) {
        //TODO write your implementation code here:
        return null;
    }

    /**
     * Web service operation for determining that an item will be return.
     * @param id
     * @param productname is the description/brand/kind of the item.
     * @param price is the price of the item.
     * @param quantity is the quantity of the item.
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "returnItem")
    public Integer returnItem(@WebParam(name = "id") int id,
            @WebParam(name = "productname") String productname,
            @WebParam(name = "price") double price,
            @WebParam(name = "quantity") int quantity,
            @WebParam(name = "skey") String skey) {
        //TODO write your implementation code here:
        return null;
    }

    /**
     * Web service operation for determining that a stack or inventory will be replenished.
     * @param id
     * @param productname is the description/brand/kind of the item.
     * @param price is the price of the item.
     * @param quantity is the quantity of the item.
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "returnStack")
    public Integer returnStack(@WebParam(name = "id") int id,
            @WebParam(name = "productname") String productname,
            @WebParam(name = "price") double price,
            @WebParam(name = "quantity") String quantity,
            @WebParam(name = "skey") String skey) {
        //TODO write your implementation code here:
        return null;
    }

    /**
     * Web service operation, returns true if the request has been added to the
     * request stack.
     * @param id
     * @param quantity
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "request")
    public Boolean request(@WebParam(name = "id") int id,
            @WebParam(name = "quantity") int quantity,
            @WebParam(name = "skey") String skey) {
        if (!skeys.containsKey(skey)) {
            System.out.println("Session key invalid...");
            return false;
        } else {
            requests.offer(new Request(rqctr, id, quantity, skey));
            rqctr++;
            System.out.println("Request has been queued...");
            return true;
        }
    }

    /**
     * Web service operation determines for the issuance of an item that is requested
     * @param id
     * @param productname is the description/brand/kind of the item.
     * @param price is the price of the item.
     * @param quantity is the quantity of the item.
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "issuance")
    public Integer issuance(@WebParam(name = "id") int id,
            @WebParam(name = "productname") String productname,
            @WebParam(name = "price") double price,
            @WebParam(name = "quantity") int quantity,
            @WebParam(name = "skey") String skey) {
        //TODO write your implementation code here:
        return null;
    }

    /**
     * Returns the updated list of stores.
     * @return the list of stores.
     */
    @WebMethod(operationName = "storeList")
    public java.util.List<Store> storeList() {
        return storeList;
    }
    
    /**
     * Returns a new unique session key. This unique session key would
     * be produced for verification purposes to be used in other operations.
     * Only the latest session key could be used by a user to ensure single
     * logins, meaning logging in the next time in the system would practically
     * disable operations that could happen in the last active session by the
     * user.
     * @return A new session key.
     */
    private String nextSessionKey() {
        char[] valid = "abcdefghijklmnopqrstuvwxyz1234567890".toCharArray();
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < SKEY_LEN; i++) {
            sb.append(valid[(int) (Math.random() * 20)]);
        }
        if (!skeys.containsKey(sb.toString())) {
            System.out.println(skeys.size());
            return sb.toString();
        } else {
            return nextSessionKey();
        }
    }

    /**
     * Web service operation
     * @param sessionKey
     * @return 
     */
    @WebMethod(operationName = "getStoreItems")
    public List<String> getStoreItems(
            @WebParam(name = "sessionKey") String sessionKey) {
        if (!skeys.containsKey(sessionKey)) {
            System.out.println("Please login...");
            return null;
        }
        List<String> products = new ArrayList<>(100);
        PreparedStatement stmt;
        ResultSet rs;
        try {
            stmt = dbcon.prepareStatement("SELECT idsi, nameprod,"
                    + " descprod, quantitysi, priceprod FROM storeinventory "
                    + "NATURAL JOIN product WHERE idsto=?");
            stmt.setInt(1, skeys.get(sessionKey));
            if (stmt.execute()) {
                rs = stmt.getResultSet();
                while (rs.next()) {
                    Product p = new Product(rs.getInt("idsi"),
                            rs.getString("nameprod"), rs.getString("descprod"),
                            rs.getInt("quantitysi"), rs.getDouble("priceprod"));
                    products.add(p.toString());
                    System.out.println("New product...");
                }
                System.out.println("Finished listing products...");
            }
        } catch (SQLException ex) {
            System.out.println("Error:" + ex.getMessage());
        }
        return products;
    }    
    
    /**
     * Web service operation
     * @return 
     */
    @WebMethod(operationName = "hasRequest")
    public Boolean hasRequest() {
        return !requests.isEmpty();
    }


    /**
     * Web service operation
     * @param skey
     * @param note
     * @return 
     */
    @WebMethod(operationName = "notifyClient")
    public Boolean notifyClient(@WebParam(name = "skey") String skey,
            @WebParam(name = "note") String note) {
        if (!skeys.containsKey(skey)) {
            System.out.println("Session key invalid...");
            return false;
        } else {
            userNotes.get(skey).offer(note);
            System.out.println("Notification has been queued...");
            return true;
        }
    }

    /**
     * Web service operation
     * @return 
     */
    @WebMethod(operationName = "getUserRequest")
    public String getUserRequest() {        
        if (requests.isEmpty()) {
            System.out.println("There are no requests available.");
            return null;
        }
        Request r = requests.remove();
        return userStore.get(r.getSkey()) + ", " + r.getRqid()+ ", " + ", " 
                + r.getId() + ", " + r.getQuantity();
    }
   
    /**
     * Web service operation
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "storeInventory")
    public List<String> storeInventory(@WebParam(name = "skey") String skey) {
        if (!skeys.containsKey(skey)) {
            System.out.println("Session key invalid...");
            return null;
        } else {
            PreparedStatement stmt;
            ResultSet rs;
            List<String> result = new ArrayList<>();

            try {
                stmt = dbcon.prepareStatement("SELECT nameprod, descprod, "
                        + "priceprod, quantitysi, idprod "
                        + "FROM storeinventory NATURAL JOIN product "
                        + "WHERE idsi=?");
                stmt.setInt(1, userStore.get(skey));
                if (stmt.execute()) {
                    rs = stmt.getResultSet();

                    while (rs.next()) {
                        Product p;
                        int id = rs.getInt("idprod");
                        String name = rs.getString("nameprod");
                        String desc = rs.getString("descprod");
                        Double price = rs.getDouble("priceprod");
                        int quantity = rs.getInt("quantitysi");
                        p = new Product(id, name, desc, quantity, price);
                        result.add(p.toString());
                    }
                } else {
                    System.out.println("User level error...");
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
                Logger.getLogger(StoreWebService.class.getName()).log(Level.SEVERE, null, ex);
            }
            System.out.println("Finished listing inventory...");
            return result;
        }
    }

    /**
     * Web service operation
     * @return 
     */
    @WebMethod(operationName = "warehouseInventory")
    public List<String> warehouseInventory() {
        Statement stmt;
        ResultSet rs;
        List<String> result = new ArrayList<>();

        try {
            stmt = dbcon.createStatement();
            if (stmt.execute("SELECT * FROM "
                    + "wareinventory NATURAL JOIN product")) {
                rs = stmt.getResultSet();

                while (rs.next()) {
                    Product p;
                    int id = rs.getInt("idprod");
                    String name = rs.getString("nameprod");
                    String desc = rs.getString("descprod");
                    Double price = rs.getDouble("priceprod");
                    int quantity = rs.getInt("quantitywi");
                    p = new Product(id, name, desc, quantity, price);
                    result.add(p.toString());
                }
            } else {
                System.out.println("User level error...");
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
            Logger.getLogger(StoreWebService.class.getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("Finished listing inventory...");
        return result;
    }

    /**
     * Web service operation
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "getStoreInfo")
    public String getStoreInfo(@WebParam(name = "skey") String skey) {
        if (!skeys.containsKey(skey)) {
            System.out.println("The session key provided is invalid...");
            return null;
        } else {
            int storeid = userStore.get(skey);
            Store s = storeList.get(storeid-1);
            return s.getName() + "," + s.getContact() + "," + s.getLoc();
        }
    }

    /**
     * Web service operation
     * @param skey
     * @return 
     */
    @WebMethod(operationName = "retrieveNote")
    public String retrieveNote(@WebParam(name = "skey") String skey) {
        if (!skeys.containsKey(skey)) {
            System.out.println("Session key invalid...");
            return "Invalid session key.";
        } else {
            if (userNotes.get(skey).isEmpty()) {
                System.out.println("No notifications for you!");
                return "No notifications.";
            }
            System.out.println("Notification has been queued...");
            return userNotes.get(skey).poll();
        }
    }

    /**
     * Web service operation
     * @param requestId
     * @param approve
     * @return 
     */
    @WebMethod(operationName = "processRequest")
    public String processRequest(@WebParam(name = "requestId") int requestId,
            @WebParam(name = "approve") boolean approve) {
        Request request = requests.peek();
        if (!requests.isEmpty() && approve && request.getRqid() == requestId) {
            try {
                requests.remove(request);
                System.out.println("Request removed.");
                if (addToStore(request.getSkey(), request.getId(),
                                request.getQuantity())) {
                    System.out.println("OK.");
                    return "Request granted.";
                } else {
                    System.out.println();
                    return "Please check the quantity of the request.";
                }      
            } catch (SQLException sqle) {
                System.out.println(sqle.getMessage());
                return "Request failed serverside error.";
            }
        } else if (approve && request.getRqid() == requestId) {
            System.out.println("Removed: " + requests.remove().getRqid() + ".");
            return "Request removed: " + requests.remove().getRqid() + ".";
        } else if (request.getRqid() != requestId) {
            System.out.println("Rqid != requestId");
            return "";
        } else {
            System.out.println("There are no requests.");
            return "";
        }
    }
    
    /**
     * 
     * @return 
     */
    private boolean addToStore(String skey, int id, int quantity)
            throws SQLException {
        PreparedStatement ps;
        ResultSet rs;
        ps = dbcon.prepareStatement("SELECT quantitywi "
                + "FROM wareinventory WHERE idprod=?");
        ps.setInt(1, id);
        System.out.println("Before executing ps.");
        if ((rs = ps.executeQuery()) != null) {
            rs.next();
            System.out.println("After executing ps.");
            int quantitywi = rs.getInt("quantitywi");
            if (quantitywi >= quantity) {
                PreparedStatement check = dbcon.prepareStatement("SELECT "
                        + "quantitysi FROM storeinventory WHERE idprod=? AND "
                        + "idsto=?");
                check.setInt(1, id);
                check.setInt(2, userStore.get(skey));
                System.out.println("Before executing check.");
                boolean flag = check.execute();
                ResultSet checkResult = check.getResultSet();
                checkResult.next();
                if (flag) {
                    int upqty = checkResult.getInt("quantitysi") + quantity;
                    PreparedStatement update = dbcon.prepareStatement("UPDATE "
                            + "storeinventory SET quantitysi=? WHERE idsto=?");
                    update.setInt(1, upqty);
                    update.setInt(2, userStore.get(skey));
                    update.executeUpdate();
                    System.out.println("Store Update: Success!");
                } else {
                    PreparedStatement insert = dbcon.prepareStatement("INSERT "
                            + "INTO storeinventory(idprod, quantitysi, idsto) "
                            + "VALUES(?,?,?)");
                    insert.setInt(1, id);
                    insert.setInt(2, quantity);
                    insert.setInt(3, userStore.get(skey));
                    insert.executeUpdate();
                    System.out.println("Insert Success!");
                }
                quantitywi -= quantity;
                PreparedStatement updateWarehouse = dbcon.prepareStatement(
                        "UPDATE wareinventory SET quantitywi=? WHERE idprod=?");
                updateWarehouse.setInt(1, quantitywi);
                updateWarehouse.setInt(2, id);
                System.out.println("Warehouse Update: Success!");
                updateWarehouse.executeUpdate();
                return true;
            } else {
                System.out.println("Quantity problem.");
                return false;
            }
        }
        return false;
    }
}
